package com.hongji.service;

import com.hongji.model.BidKeyword;
import com.hongji.model.RawBidInfo;
import com.hongji.util.JsonFileReader;
import lombok.extern.slf4j.Slf4j;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;
import java.sql.Date;
import java.time.LocalDate;
import java.util.*;

@Slf4j
public class BidImportService {

    private final EmailService emailService;

    private static final String DB_URL = "jdbc:mysql://localhost:3306/dev?useSSL=false&useUnicode=true&characterEncoding=utf8";
    private static final String USER = "lee";
    private static final String PASSWORD = "123456";
    private static final LocalDate YESTERDAY = LocalDate.now().minusDays(1);

    public BidImportService() {
        this.emailService = new EmailService(
                "smtp.163.com",
                465,
                "lfl_frank@163.com",
                "CFTqX36ZqWaxaAKb",
                "招标监控系统 <lfl_frank@163.com>",
//                "591904745@qq.com" //测试时只用自己的邮箱
                "591904745@qq.com,HNHJGK@163.com"// 收件人列表（多个收件人）
        );
    }

    public void processAllJsonFiles(String rootDirs) {
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
            log.info("开始删除旧的采集数据");
            clearRawTable(conn);

            log.info("收集并处理所有JSON文件");
            JsonFileReader.processFilesAndSaveToDB(rootDirs, conn);

            List<List<RawBidInfo>> bidInfoLists = loadAllRecords(conn);
            log.info("共加载到{}条有效数据", bidInfoLists.stream().mapToInt(List::size).sum());

            saveFilteredBids(conn, bidInfoLists);

            log.info("准备发送邮件...");
            emailService.sendBidInfoEmail(bidInfoLists, YESTERDAY);
            log.info("邮件发送成功");

            log.info("开始清理临时文件");
             clearDirectory(rootDirs); // 注释掉以防误删
            log.info("临时文件清理完成");

        } catch (Exception e) {
            log.error("处理文件和数据库操作时发生错误: ", e);
        }
    }

    private void clearRawTable(Connection conn) {
        try (Statement stmt = conn.createStatement()) {
            int deleted = stmt.executeUpdate("DELETE FROM raw_bid_info");
            log.info("已删除 raw_bid_info 表中的 {} 条数据", deleted);
        } catch (SQLException e) {
            log.error("清理 raw_bid_info 表时发生错误", e);
        }
    }

    public static void clearDirectory(String rootDirs) {
        String[] paths = rootDirs.split(",");
        for (String directoryPath : paths) {
            Path path = Paths.get(directoryPath.trim());
            try {
                Files.walk(path)
                        .filter(file -> !file.equals(path))
                        .sorted(Comparator.reverseOrder())
                        .forEach(file -> deleteFileOrDirectory(file));
            } catch (IOException e) {
                log.error("遍历目录失败: {} - {}", path, e.getMessage());
            }
        }
    }

    private static void deleteFileOrDirectory(Path file) {
        try {
            if (Files.isRegularFile(file)) {
                Files.delete(file);
                log.info("已删除文件: {}", file);
            } else if (Files.isDirectory(file)) {
                Files.delete(file);
                log.info("已删除文件夹: {}", file);
            }
        } catch (IOException e) {
            log.error("删除文件失败: {} - {}", file, e.getMessage());
        }
    }

    private List<List<RawBidInfo>> loadAllRecords(Connection conn) throws SQLException {
        List<List<RawBidInfo>> bidInfoLists = new ArrayList<>();
        List<RawBidInfo> craneList = new ArrayList<>();
        List<RawBidInfo> yascawaList = new ArrayList<>();

        String sql = "SELECT title, url, raw_publish_text, end_raw_publish_text FROM raw_bid_info";

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                RawBidInfo record = new RawBidInfo();
                String title = rs.getString("title");
                record.setTitle(title);
                record.setUrl(rs.getString("url"));
                record.setRawPublishText(rs.getString("raw_publish_text"));
                record.setEndRawPublishText(rs.getString("end_raw_publish_text"));

                if (title.contains(BidKeyword.YASKAWA.getValue())) {
                    yascawaList.add(record);
                } else {
                    craneList.add(record);
                }
            }
            bidInfoLists.add(craneList);
            bidInfoLists.add(yascawaList);
        }
        return bidInfoLists;
    }

    private void saveFilteredBids(Connection conn, List<List<RawBidInfo>> bidInfoLists) throws SQLException {
        String sql = "INSERT INTO filtered_bid_info (title, url, publish_date, end_date) VALUES (?, ?, ?, ?)";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (List<RawBidInfo> list : bidInfoLists) {
                for (RawBidInfo info : list) {
                    ps.setString(1, info.getTitle());
                    ps.setString(2, info.getUrl());

                    if (Objects.nonNull(info.getRawPublishText())) {
                        ps.setDate(3, Date.valueOf(info.getRawPublishText()));
                    } else {
                        ps.setString(3, "");
                    }

                    if (Objects.nonNull(info.getEndRawPublishText())) {
                        ps.setDate(4, Date.valueOf(info.getEndRawPublishText()));
                    } else {
                        ps.setString(4, "");
                    }

                    ps.addBatch();
                }
            }
            ps.executeBatch();
            log.info("已成功批量插入过滤后的数据");
        }
    }
}
